package cam.School.DataAccessObjectImpl;

import static cam.School.DataAccessObject.DAOUtil.close;
import static cam.School.DataAccessObject.DAOUtil.prepareStatement;
import static cam.School.DataAccessObject.DAOUtil.toSqlDate;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import cam.School.DAOException.DAOException;
import cam.School.DataAccessObject.DAOFactory;
import cam.School.DataAccessObject.StudentDAO;
import cam.School.DataTransfertObject.CallResult;
import cam.School.DataTransfertObject.ClassRoomDTO;
import cam.School.DataTransfertObject.EmployeeDTO;
import cam.School.DataTransfertObject.StudentDTO;

public class StudentDAOImpl implements StudentDAO {

	// Constants ----------------------------------------------------------------------------------
	
    private static final String QUERY_PARAMS_GET_ALL ="id, classroomId, firstname, matriculationNumber,"+
                         " lastname, street, city, zipCode, country, birthday, email, cellPhone,"+
                         " personToCallByEmergencyCase,placeOfBirth, numberToCallByEmergencyCase,"+
                         " firstRegistration, lastUpdate, gender, fatherName, motherName, createdBy";
        
    private static final String UPDATE_QUERY_PARAMS = "classroomId = ?, firstname = ?,"
			+ " matriculationNumber = ?, lastLogin = ?,"
			+ " lastname = ?, street = ?, city = ?, zipCode = ?, country = ?,"
			+ " email = ?, cellPhone = ?,"
			+ " personToCallByEmergencyCase = ?, numberToCallByEmergencyCase = ?,"
			+ " birthday = ?, placeOfBirth = ?,"
			+ " lastUpdate = ?, gender = ?,"
			+ " fatherName = ?, motherName = ?";
    
    private static final String STUDENT_PARAMS_GET_ALL = "student.id as id, classRoomId,"  
			+"firstname,lastname, street, city, zipCode,"
			+"country, birthday, email, cellPhone, personToCallByEmergencyCase,"
			+"numberToCallByEmergencyCase, student.firstRegistration, matriculationNumber,"
			+"placeOfBirth, gender, fatherName, motherName, student.lastUpdate, createdBy";
    
    
    private static final String SQL_FIND_BY_CLASSGROUP = "SELECT "
			+  STUDENT_PARAMS_GET_ALL
			+" FROM infrastructurcategory " 
			+" inner join classroom on infrastructurcategory.id = classroom.infrastructureCategoryId "
			+" inner join student on student.classRoomId = classroom.id "
			+" where infrastructurcategory.name = ? AND student.isDeleted = 0 ";
    
    private static final String SQL_FIND_BY_CLASSNAME = "SELECT "
			+  QUERY_PARAMS_GET_ALL
			+" FROM student"
			+" where student.classRoomId = ? AND student.isDeleted = 0";
    
    
    private static final String SQL_FIND_IMAGE_BY_STUDENT_ID = "SELECT location " 
			+ " FROM Image WHERE  objectId = ?";
    
    private static final String SQL_FIND_ALL = "SELECT "
			+ QUERY_PARAMS_GET_ALL
			+ " FROM student WHERE isDeleted = 0";
    
    private static final String SQL_FIND_BY_ID =
        "SELECT "+ QUERY_PARAMS_GET_ALL +" FROM student WHERE  id = ? AND isDleted = 0";
    
    private static final String SQL_FIND_BY_IMMATRICULATION_NUMBER =
            "SELECT "+ QUERY_PARAMS_GET_ALL +" FROM student WHERE  matriculationNumber = ? AND isDeleted = 0";
    
    private static final String SQL_FIND_BY_FIRSTNAME_AND_LASTNAME =
    	"SELECT "+ QUERY_PARAMS_GET_ALL +" FROM student join picture WHERE student.id = picture.objectId AND firstname = ? AND lastname = ? AND isDeleted = 0";
    
    private static final String SQL_FIND_BY_FIRSTNAME_AND_BIRTHDAY =
        	"SELECT "+ QUERY_PARAMS_GET_ALL +" FROM student join picture WHERE student.id = picture.objectId AND birthday = ? AND lastname = ? AND isDeleted = 0";
        
    private static final String SQL_UPDATE_BY_LASTNAME_AND_BIRTHDAY =
        	"UPDATE user SET "+ UPDATE_QUERY_PARAMS +" FROM student WHERE lastname = ? AND birthday = ? AND isDeleted = 0";
    
    
    private static final String SQL_DELETE_BY_ID = " UPDATE student SET isDeleted = 1 WHERE id = ?";

    private static final String SQL_UPDATE = "UPDATE employee SET "
			+ UPDATE_QUERY_PARAMS
			+ " FROM student WHERE id = ? AND isDeleted = 0";
    
    private static final String SQL_GET_CLASSROOM_CAPACITY = "SELECT count(classRoomId) as numberOfStudent, classRoomId FROM camschooldb.student group by classRoomId";
    // Vars ---------------------------------------------------------------------------------------

    private DAOFactory daoFactory;
    
 // Constructors -------------------------------------------------------------------------------

    /**
     * Construct an User DAO for the given DAOFactory. Package private so that it can be constructed
     * inside the DAO package only.
     * @param daoFactory The DAOFactory to construct this User DAO for.
     */
    public StudentDAOImpl(DAOFactory daoFactory) {
        this.daoFactory = daoFactory;
    }

    private StudentDTO map(ResultSet resultSet)
			throws SQLException {
    	StudentDTO student = new StudentDTO();
		student.setId(resultSet.getLong("id"));
		student.setFirstname(resultSet.getString("firstname"));
		student.setLastname(resultSet.getString("lastname"));
		student.setStreet(resultSet.getString("street"));
		student.setCity(resultSet.getString("city"));
		student.setZipCode(resultSet.getString("zipCode"));
		student.setCountry(resultSet.getString("country"));
		student.setEmail(resultSet.getString("email"));
		student.setMatriculationNumber(resultSet.getString("matriculationNumber"));
		student.setBirthday(resultSet.getDate("birthday"));
		student.setClassRoomId(resultSet.getLong("classRoomId"));
		student.setPlaceOfBirth(resultSet.getString("placeOfBirth"));
		student.setCellPhone(resultSet.getString("cellPhone"));
		student.setNumberToCallByEmergencyCase(resultSet
				.getString("numberToCallByEmergencyCase"));
		student.setPersonToCallByEmergencyCase(resultSet
				.getString("personToCallByEmergencyCase"));
		student.setFirstRegistration(resultSet.getDate("firstRegistration"));
		student.setLastUpdate(resultSet.getDate("lastUpdate"));
	    student.setPlaceOfBirth(resultSet.getString("placeOfBirth"));
	    student.setGender(resultSet.getString("gender"));
	    student.setFatherName(resultSet.getString("fatherName"));
	    student.setMotherName(resultSet.getString("motherName"));	    
		student.setCreatedBy(resultSet.getString("createdBy"));
		student.setPictureLocationPath(getStudentImagePathLocation(student.getId()));	
		return student;
	}
    
    private String getStudentImagePathLocation(long studentId){
		return getStudentImagePathLocation(SQL_FIND_IMAGE_BY_STUDENT_ID, studentId);		
	}
    
    private String getStudentImagePathLocation(String sql, Object... values) throws DAOException {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;

		try {
			connection = daoFactory.getConnection();
			preparedStatement = prepareStatement(connection, sql, false, values);
			resultSet = preparedStatement.executeQuery();
			if (resultSet.next()) {
				return resultSet.getString("location");
			}
		} catch (SQLException e) {
			return "default Location";
		} finally {
			close(connection, preparedStatement, resultSet);
		}
		return "default Location"; //TO DO 
	}
    
	@Override
	public StudentDTO getById(Long id) throws DAOException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public List<StudentDTO> getAll() throws DAOException {
		Object[] values = {};
		return getAll(SQL_FIND_ALL, values);
	}
	
	public List<StudentDTO> getAll(String sqlQuery, Object... values) throws DAOException {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		List<StudentDTO> students = new ArrayList<StudentDTO>();

		try {
			connection = daoFactory.getConnection();
			if(values.length == 0 ){
				preparedStatement = connection.prepareStatement(sqlQuery);
			}else{
				preparedStatement = prepareStatement(connection, sqlQuery, false, values);
			}
			
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				students.add(map(resultSet));
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(connection, preparedStatement, resultSet);
		}
		return students;
	}

	@Override
	public CallResult create(StudentDTO student) throws IllegalArgumentException,
			DAOException {
		Connection connection = null;
		CallableStatement callableStatement = null;
		long result;
		try {
			connection = daoFactory.getConnection();
			System.out.println("2: "+student.getClassRoomId()+
					"3: "+student.getFirstname()+
					"4: "+student.getLastname()+
					"5: "+student.getStreet()+
					"6: "+student.getCity()+
					"7: "+student.getZipCode()+
					"8: "+student.getCountry()+
					"9: "+student.getBirthday()+
					"10: "+student.getEmail()+
					"11: "+student.getCellPhone()+
					"12: "+student.getPersonToCallByEmergencyCase()+
					"13: "+student.getNumberToCallByEmergencyCase()+
					"14: "+student.getMatriculationNumber()+
					"15: "+student.getPlaceOfBirth()+
					"16: "+student.getGender()+
					"17: "+student.getFatherName()+
					"18: "+student.getMotherName()+
					"19: "+student.getCreatedBy());
			callableStatement = connection.prepareCall("{call createStudentProcedure(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");			
			
			callableStatement.registerOutParameter(1, java.sql.Types.BIGINT);
			callableStatement.setLong(2, student.getClassRoomId());
			callableStatement.setString(3, student.getFirstname());
			callableStatement.setString(4, student.getLastname());			
			callableStatement.setString(5, student.getStreet());
			
			callableStatement.setString(6, student.getCity());
			callableStatement.setString(7, student.getZipCode());
			callableStatement.setString(8, student.getCountry());
			callableStatement.setDate(9, toSqlDate(student.getBirthday()));						
			callableStatement.setString(10, student.getEmail());
			callableStatement.setString(11, student.getCellPhone());
			callableStatement.setString(12, student.getPersonToCallByEmergencyCase());
			callableStatement.setString(13, student.getNumberToCallByEmergencyCase());
			callableStatement.setBoolean(14, false);
			callableStatement.setDate(15, toSqlDate(new Date()));
			callableStatement.setString(16, student.getMatriculationNumber());
			callableStatement.setString(17, student.getPlaceOfBirth());
			callableStatement.setString(18, student.getGender());
			callableStatement.setString(19, student.getFatherName());
			callableStatement.setString(20, student.getMotherName());
			callableStatement.setDate(21, toSqlDate(new Date()));
			callableStatement.setString(22, student.getCreatedBy());		
			
			callableStatement.executeUpdate();			
			result = callableStatement.getLong(1);
			
			if(new Integer((int)result).equals(new Integer(0))){
				System.out.println("not duplicated allowed");
				return new CallResult(406,"not duplicated allowed");
			}
			
		} catch (SQLException e) {
			System.out.println("error: "+e.toString());
			return new CallResult(406,e.toString());
		} finally {
			close(connection, callableStatement);
		}
		return new CallResult(result,"success");		
	}

	@Override
	public CallResult update(StudentDTO student) throws IllegalArgumentException,
			DAOException {
		
        Object[] values = {
        		student.getClassRoomId(),
        		student.getFirstname(),
        		student.getMatriculationNumber(),
        		student.getLastname(),
        		student.getStreet(),
        		student.getCity(),
        		student.getZipCode(),
        		student.getCountry(),
        		student.getEmail(),
        		student.getCellPhone(),
        		student.getPersonToCallByEmergencyCase(),
        		student.getNumberToCallByEmergencyCase(),  
        		toSqlDate(student.getBirthday()),
        		student.getPlaceOfBirth(),
        		student.getLastUpdate(),
        		student.getGender(),
        		student.getFatherName(),
        		student.getMotherName(),        
        		student.getId()
        };
                
        
        try (
            Connection connection = daoFactory.getConnection();
            PreparedStatement statement = prepareStatement(connection, SQL_UPDATE, false, values);
        ) {
            int affectedRows = statement.executeUpdate();
            if (affectedRows == 0) {
            	return new CallResult(406, "Updating student failed, no rows affected.");
            }
        } catch (SQLException e) {
        	return new CallResult(406, e.toString());
        }
        return new CallResult(200, "success");		
		
	}

	@Override
	public CallResult delete(long studentId) throws Exception {
		Connection connection = null;
		PreparedStatement deleteByIdPreparedStatement = null;
				
		try {
			connection = daoFactory.getConnection();
			connection.setAutoCommit(false);
			deleteByIdPreparedStatement = connection.prepareStatement(SQL_DELETE_BY_ID);
			deleteByIdPreparedStatement.setLong(1, studentId);
			deleteByIdPreparedStatement.executeUpdate();
			
			connection.commit();			
		} catch (SQLException e) {
			connection.rollback();
			new CallResult(405, e.toString());
		} finally {
			close(connection, deleteByIdPreparedStatement);
		}
		return new CallResult(200, "sucess");
	}

	
	public List<StudentDTO> getClassRoomCapacity() throws DAOException {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		List<StudentDTO> students = new ArrayList<StudentDTO>();

		try {
			connection = daoFactory.getConnection();
			preparedStatement = connection.prepareStatement(SQL_GET_CLASSROOM_CAPACITY);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				students.add(mapClassRoomCapacity(resultSet));
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(connection, preparedStatement, resultSet);
		}

		return students;
	}
	
	private static StudentDTO mapClassRoomCapacity(ResultSet resultSet) throws SQLException {
		StudentDTO student = new StudentDTO();
		student.setId(resultSet.getLong("classRoomId"));
		student.setClassRoomId(resultSet.getLong("numberOfStudent"));
		return student;
	}
	
	@Override
	public StudentDTO getByFirstName(String firstName) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public StudentDTO getByFirstAndLastName(String firstName, String lastName) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public StudentDTO getByLastNameAndBirthday(Date birthday, String lastName) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public List<StudentDTO> getByClassRoom(long classRoomId) {
		Object[] values = {classRoomId};
		return getAll(SQL_FIND_BY_CLASSNAME, values);	
	}

	
	
	@Override
	public List<StudentDTO> getByClassRoomGroup(String className) {
		Object[] values = {className};
		return getAll(SQL_FIND_BY_CLASSGROUP, values);	
	}

	
	
	@Override
	public StudentDTO getByImmatricutionNumber(String immatriculationNumber) {
		Object[] values = {immatriculationNumber};
		List<StudentDTO> students = getAll(SQL_FIND_BY_IMMATRICULATION_NUMBER, values);
		
		if(students.size() > 0){
			return students.get(0);
		}
		return null;
	}
}
